--------------------------------------------------------------------------------
-- @name: explain vs runtime
-- @author: dion cho
-- @note: test case for the difference between explain plan and runtime plan
--------------------------------------------------------------------------------

set pages 100
set lines 200
set timing off
set trimspool on

@capture_on

-- basics of execution plan (explain, runtime, awr, tuning set, baseline)
-- the components of execution plan (all + peeked_binds)

select * from v$version where rownum = 1;

-- create objects
drop table t1 purge;

create table t1(c1 int, c2 varchar2(10));

insert into t1 select 1, '1' from dual connect by level <= 10000;
insert into t1 select level, level from dual connect by level <= 10000;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c2);

exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 5');

@tab_stat t1

-- case1: bind peeking
alter session set "_optim_peek_user_binds" = true;

var b1 number;
exec :b1 := 1;

explain plan for
select count(c2) 
from t1
where c1 = :b1;

select * from table(dbms_xplan.display(null, null, 'typical'));

select /*+ gather_plan_statistics */ count(c2) 
from t1
where c1 = :b1;

select * from table(dbms_xplan.display_cursor(null, null, 'typical'));

-- the components of the execution plan
select /*+ gather_plan_statistics */ count(c2) 
from t1
where c1 = :b1;

select * from table(dbms_xplan.display_cursor(null, null, 'all +peeked_binds'));


select /*+ gather_plan_statistics */ count(c2) 
from t1
where c1 = :b1;

select * from table(dbms_xplan.display_cursor(null, null, 'typical +peeked_binds'));

select /*+ gather_plan_statistics */ count(c2) 
from t1
where c1 = :b1;

-- check v$sql_plan.other_xml
col prev_sql_id new_value prev_sql_id

select prev_sql_id from v$session where sid = userenv('sid');

set long 10000

select other_xml from v$sql_plan where sql_id = '&prev_sql_id';

-- v$sql_bind_capture
col value_string format a10
col name format a10

select name, position, value_string, was_captured, last_captured
from v$sql_bind_capture
where sql_id = '&prev_sql_id';

@para bind_capture

-- case2: data type conversion
alter session set "_optim_peek_user_binds" = false;

var b2 number;
exec :b2 := 1;

var b3 varchar2(10);
exec :b3 := '1';

explain plan for
select count(c1)
from t1
where c2 = :b2;

select * from table(dbms_xplan.display(null, null, 'typical'));

select /*+ gather_plan_statistics */ count(c1)
from t1
where c2 = :b2;

@stat

select /*+ gather_plan_statistics */ count(c1)
from t1
where c2 = :b3;

@stat

explain plan for
select count(c1)
from t1
where c2 = :b4;

@plan


-- case3: invalidation delay
explain plan for
select count(c2) 
from t1
where c1 = 2;

@plan

select /*+ gather_plan_statistics */ 
	count(c2)
from t1
where c1 = 2;

@stat


insert into t1 select 2, '1' from dual connect by level <= 20000;

exec dbms_stats.gather_table_stats(user, 't1', -
	method_opt=>'for columns c1 size 5', no_invalidate=>null);

explain plan for
select count(c2) 
from t1
where c1 = 2;

@plan

select /*+ gather_plan_statistics */ 
	count(c2)
from t1
where c1 = 2;

@stat

@capture_off

